package com.bango.flex{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	
	import spark.components.List;
	
	public class BangoDatabaseStorage{
		
		private var _connection:SQLConnection;
		private var _insertProfileStatement:SQLStatement = new SQLStatement();
		private var _insertEventStatement:SQLStatement = new SQLStatement();
		private var _insertEventParameterStatement:SQLStatement = new SQLStatement();
		private var _profileId:Number;
		private var _persistDatabaseConnection:Boolean;

		public function getProfileId():Number{
			return _profileId;
		}
		public function setProfileId(profileId:Number):void{
			_profileId=profileId;
		}
	
		public function getPersistDatabaseConnection():Boolean{
			return _persistDatabaseConnection;
		}
		
		public function setPersistDatabaseConnection(value:Boolean):void{
			_persistDatabaseConnection = value;
		}		
		
		private function ProfileTableName():String{
			return BangoConstants.DATABASE_TABLENAMEPROFILE + "_" + BangoConstants.DATABASE_VERSION.toString();
		}
		
		private function EventTableName():String{
			return BangoConstants.DATABASE_TABLENAMEEVENT + "_" + BangoConstants.DATABASE_VERSION.toString();
		}
		
		private function EventParameterTableName():String{
			return BangoConstants.DATABASE_TABLENAMEEVENTPARAMETER + "_" + BangoConstants.DATABASE_VERSION.toString();
		}		
		
		public function BangoDatabaseStorage(){
			
			OpenDatabase();
			CreateTables();
			CompactDatabase();
			
		}
		
		private function CreateTables():void{
			try{
				execute(BangoConstants.DATABASE_TABLECREATEPROFILE.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName()));
				execute(BangoConstants.DATABASE_TABLECREATEEVENT.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventTableName()));
				execute(BangoConstants.DATABASE_TABLECREATEEVENTPARAMETER.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventParameterTableName()));
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
		}
		
		private function CompactDatabase():void{
			execute("VACUUM;");
		}
		
		private function OpenDatabase():void{
			
			if(_connection==null || !_connection.connected){
				
				var dbFile:File = File.applicationStorageDirectory.resolvePath(BangoConstants.DATABASE_NAME);
				_connection = new SQLConnection();
				_connection.open(dbFile);
				
				_insertProfileStatement.text = BangoConstants.DATABASE_CREATEPROFILE.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName());
				_insertProfileStatement.sqlConnection = _connection;
				
				_insertEventStatement.text = BangoConstants.DATABASE_CREATEEVENT.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventTableName());
				_insertEventStatement.sqlConnection = _connection;
				
				_insertEventParameterStatement.text = BangoConstants.DATABASE_CREATEEVENTPARAMETER.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventParameterTableName());
				_insertEventParameterStatement.sqlConnection = _connection;
			}
			
		}	
		
		public function CloseDatabase():void{
			_connection.close();
		}
		
		private function execute(sql:String):SQLStatement{
			var statement:SQLStatement =null;
			try{
				statement= new SQLStatement();
				statement.text = sql;
				statement.sqlConnection = _connection;
				statement.execute();
			}catch(error:Error){}
			return statement;
		}
		
		public function LogProfile(bangoProfile:BangoProfile):void{
			LogProfileValues(
				bangoProfile.SDKType,
				bangoProfile.SDKVersion,
				bangoProfile.ApplicationId,
				bangoProfile.ApplicationType,
				bangoProfile.ApplicationChannel,
				bangoProfile.ApplicationVersion,
				bangoProfile.UserId,
				bangoProfile.ConnectionType,
				bangoProfile.IsRoaming,
				bangoProfile.LocationSource,
				bangoProfile.LocationZ,
				bangoProfile.LocationY,
				bangoProfile.LocationX,
				bangoProfile.LocationAccuracy,
				bangoProfile.Operator,
				bangoProfile.OperatorName,
				bangoProfile.CountryISO,
				bangoProfile.SIM,
				bangoProfile.DeviceBrand,
				bangoProfile.DeviceMake,
				bangoProfile.DeviceModel,
				bangoProfile.Product,
				bangoProfile.OperatingSystem,
				bangoProfile.OperatingSystemVersion,
				bangoProfile.DeviceVersion,
				bangoProfile.OperatorConnectionType);
		}
		
		public function LogProfileValues(sdkType:Number, sdkVersion:String, applicationId:String, applicationType:String, applicationChannel:String, applicationVersion:String, userId:String, connectionType:String, isRoaming:Boolean, locationSource:String, locationAltitude:Number, locationLatitude:Number, locationLongitude:Number, locationAccuracy:Number, operator:String, operatorName:String, operatorCountryIso:String, simOperator:String, brand:String, device:String, model:String, product:String, os:String, version:String, buildId:String, operatorConnectionType:String):void{
			var isRoamingInt:Number = isRoaming ? 1 : 0;
			
			if (applicationId == null){applicationId = "";}
			if (applicationType == null){applicationType = "";}
			if (applicationChannel == null){applicationChannel = "";}
			if (applicationVersion == null){applicationVersion = "";}
			if (userId == null){userId = "";}
			if (locationSource == null){locationSource = "";}
			if (operator == null){operator = "";}
			if (operatorCountryIso == null){operatorCountryIso = "";}
			if (simOperator == null){simOperator = "";}
			if (brand == null){brand = "";}
			if (device == null){device = "";}
			if (model == null){model = "";}
			if (product == null){product = "";}
			if (os == null){os = "";}
			if (version == null){version = "";}
			if (buildId == null){buildId = "";}
			
			OpenDatabase();
			
			_insertProfileStatement.clearParameters();
			_insertProfileStatement.parameters[0]= sdkType;
			_insertProfileStatement.parameters[1]= sdkVersion;
			_insertProfileStatement.parameters[2]= applicationId;
			_insertProfileStatement.parameters[3]= applicationType;
			_insertProfileStatement.parameters[4]= applicationChannel;
			_insertProfileStatement.parameters[5]= applicationVersion;
			
			_insertProfileStatement.parameters[6]= userId;
			
			_insertProfileStatement.parameters[7]= 10;
			_insertProfileStatement.parameters[8]= connectionType;
			_insertProfileStatement.parameters[9]= isRoamingInt;
			
			_insertProfileStatement.parameters[10]= locationSource;
			_insertProfileStatement.parameters[11]= locationAltitude;
			_insertProfileStatement.parameters[12]= locationLatitude;
			_insertProfileStatement.parameters[13]= locationLongitude;
			_insertProfileStatement.parameters[14]= locationAccuracy;
			
			_insertProfileStatement.parameters[15]= operator;
			_insertProfileStatement.parameters[16]= operatorName;
			_insertProfileStatement.parameters[17]= operatorCountryIso;
			_insertProfileStatement.parameters[18]= simOperator;
			_insertProfileStatement.parameters[19]= brand;
			_insertProfileStatement.parameters[20]= device;
			_insertProfileStatement.parameters[21]= model;
			_insertProfileStatement.parameters[22]= product;
			_insertProfileStatement.parameters[23]= os;
			_insertProfileStatement.parameters[24]= version;
			_insertProfileStatement.parameters[25]= buildId;
			
			_insertProfileStatement.parameters[26]= operatorConnectionType;
			_insertProfileStatement.parameters[27]= false;
			_insertProfileStatement.parameters[28]= false;
			
			
			
			try{
				 _insertProfileStatement.execute();
				 var result:SQLResult = _insertProfileStatement.getResult();
				 _profileId = result.lastInsertRowID as Number;
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
		}
		
		public function UpdateProfileLocation(locationSource:String,locationLatitude:Number,locationLongitude:Number,locationAltitude:Number,locationAccuracy:Number):void{

			OpenDatabase();		
			try{
				execute(BangoConstants.DATABASE_UPDATEPROFILELOCATION.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName()).replace(BangoConstants.DATABASE_PARAMETER1_PLACEHOLDER, locationLatitude.toString()).replace(BangoConstants.DATABASE_PARAMETER2_PLACEHOLDER, locationLongitude.toString()).replace(BangoConstants.DATABASE_PARAMETER3_PLACEHOLDER, locationAltitude.toString()).replace(BangoConstants.DATABASE_PARAMETER4_PLACEHOLDER, locationAccuracy.toString()).replace(BangoConstants.DATABASE_PARAMETER5_PLACEHOLDER, locationSource) + _profileId.toString());
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
		}
		
		public function LogEvent(event:BangoEvent):Number{
			return LogEventValues(event.ConnectionType,
				event.OperatorConnectionType,
				event.ErrorLevel,
				event.EventName,
				event.EventDetail,
				event.EventValue,
				event.ErrorId,
				event.EventTime,
				event.EventLocalTime,
				event.EventParameters);
		}
		
		public function LogEventValues(connectionType:String, operatorConnectionType:String, errorLevel:Number, eventName:String, eventDetail:String, eventValue:String, errorId:String, eventTime:String,eventLocalTime:String,eventParameters:Array):Number{
			
			var eventId:Number = -1;
			if (eventName == null){eventName = "";}
			if (eventDetail == null){eventDetail = "";}
			if (eventValue == null){eventValue = "";}
			if (errorId == null){errorId = "";}
			
			OpenDatabase();

			_insertEventStatement.clearParameters();
			
			trace("Log Event " + eventName + " for profile " + _profileId);
			
			_insertEventStatement.parameters[0]= _profileId;
			_insertEventStatement.parameters[1]= eventName;
			_insertEventStatement.parameters[2]= eventDetail;
			_insertEventStatement.parameters[3]= eventValue;
			_insertEventStatement.parameters[4]= errorId;
			_insertEventStatement.parameters[5]= eventTime;
			_insertEventStatement.parameters[6]= eventLocalTime;
			_insertEventStatement.parameters[7]= connectionType;
			_insertEventStatement.parameters[8]= operatorConnectionType;
			_insertEventStatement.parameters[9]= errorLevel;
			_insertEventStatement.parameters[10]= 0;
			
			try{
				_insertEventStatement.execute();
				var result:SQLResult = _insertEventStatement.getResult();
				eventId = result.lastInsertRowID as Number;
				trace("Logged Event id " + eventId);

				LogEventParameter(eventId,eventParameters);
				
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
			return eventId;
		}
		
		private function LogEventParameter(eventId:Number,eventParameters:Array):void{
		
			for(var i:Object in eventParameters){
				var name:String = eventParameters[i][BangoFieldNames.POST_PARAMETERNAME];
				var value:String = eventParameters[i][BangoFieldNames.POST_PARAMETERVALUE];
				
				if (name == null){name = "";}
				if (value == null){value = "";}
				
				_insertEventParameterStatement.clearParameters();
				_insertEventParameterStatement.parameters[0]= eventId;
				_insertEventParameterStatement.parameters[1]= name;
				_insertEventParameterStatement.parameters[2]= value;
				
				try{
					_insertEventParameterStatement.execute();
				}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
				
			}
		
		}
		
		public function GetProfilesForPost(notCurrentProfile:Boolean):Array{
			OpenDatabase();
			
			var notProfileId:Number = 0;
			var result:SQLResult=null;
			var statement:SQLStatement =null;
			var profiles:Array = new Array();
			
			if (notCurrentProfile){
				notProfileId = _profileId;
			}
			
			try{
				statement= new SQLStatement();
				statement.text = BangoConstants.DATABASE_SELECTPOSTPROFILES.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName());
				statement.sqlConnection = _connection;
				statement.parameters[0] = notProfileId;
				statement.parameters[1] = notProfileId;
				statement.execute();
				result = statement.getResult();
				
				for(var i:Object in result.data){
					var obj:Object = result.data[i];
					
					var prof:BangoProfile = new BangoProfile(
						obj[BangoConstants.DATABASE_COLUMN_SDKTYPE], 
						obj[BangoConstants.DATABASE_COLUMN_SDKVERSION], 
						obj[BangoConstants.DATABASE_COLUMN_APPLICATIONID], 
						obj[BangoConstants.DATABASE_COLUMN_APPLICATIONTYPE], 
						obj[BangoConstants.DATABASE_COLUMN_APPLICATIONCHANNEL], 
						obj[BangoConstants.DATABASE_COLUMN_APPLICATIONVERSION], 
						obj[BangoConstants.DATABASE_COLUMN_USERID], 
						obj[BangoConstants.DATABASE_COLUMN_CONNECTIONTYPE], 
						obj[BangoConstants.DATABASE_COLUMN_ISROAMING], 
						obj[BangoConstants.DATABASE_COLUMN_LOCATIONSOURCE],
						obj[BangoConstants.DATABASE_COLUMN_LOCATIONALTITUDE], 
						obj[BangoConstants.DATABASE_COLUMN_LOCATIONLATITUDE], 
						obj[BangoConstants.DATABASE_COLUMN_LOCATIONLONGITUDE],
						obj[BangoConstants.DATABASE_COLUMN_LOCATIONACCURACY],
						obj[BangoConstants.DATABASE_COLUMN_OPERATOR], 
						obj[BangoConstants.DATABASE_COLUMN_OPERATORNAME],
						obj[BangoConstants.DATABASE_COLUMN_OPERATORCOUNTRYISO],
						obj[BangoConstants.DATABASE_COLUMN_BRAND],
						obj[BangoConstants.DATABASE_COLUMN_DEVICE],
						obj[BangoConstants.DATABASE_COLUMN_MODEL],
						obj[BangoConstants.DATABASE_COLUMN_PRODUCT],
						obj[BangoConstants.DATABASE_COLUMN_OS], 
						obj[BangoConstants.DATABASE_COLUMN_VERSION], 
						obj[BangoConstants.DATABASE_COLUMN_BUILDID],
						obj[BangoConstants.DATABASE_COLUMN_OPERATORCONNECTIONTYPE], 
						obj[BangoConstants.DATABASE_COLUMN_SIMOPERATOR] );
					
					prof.setProfileId(obj[BangoConstants.DATABASE_COLUMN_PROFILEID]);
					profiles.push(prof);
				}
				
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
			return profiles;
		
		}
			
		public function GetEventsForPost(profileId:Number):Array{
			
			OpenDatabase();
			
			var result:SQLResult=null;
			var statement:SQLStatement =null;
			var events:Array = new Array();
			
			try{
				statement= new SQLStatement();
				statement.text = BangoConstants.DATABASE_SELECTPOSTEVENTS.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventTableName());
				statement.sqlConnection = _connection;
				statement.parameters[0] = profileId.toString();
				statement.execute();
				result = statement.getResult();
			
				
				for(var i:Object in result.data){
					var obj:Object = result.data[i];
					
					var eventParameters:Array = new Array();
					var eventId:Number = obj[BangoConstants.DATABASE_COLUMN_EVENTID];
					var event:BangoEvent = new BangoEvent(
						eventId,
						obj[BangoConstants.DATABASE_COLUMN_CONNECTIONTYPE],
						obj[BangoConstants.DATABASE_COLUMN_OPERATORCONNECTIONTYPE],
						obj[BangoConstants.DATABASE_COLUMN_ERRORLEVEL],
						obj[BangoConstants.DATABASE_COLUMN_EVENTNAME],
						obj[BangoConstants.DATABASE_COLUMN_EVENTDETAIL],
						obj[BangoConstants.DATABASE_COLUMN_EVENTVALUE],
						obj[BangoConstants.DATABASE_COLUMN_ERRORID],
						obj[BangoConstants.DATABASE_COLUMN_EVENTTIME],
						obj[BangoConstants.DATABASE_COLUMN_EVENTLOCALTIME],
						GetEventsParametersForEvent(eventId));
					
					event.setProfileId(obj[BangoConstants.DATABASE_COLUMN_PROFILEID]);
					events.push(event);
				}
			
				
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
			return events;
		
		}
		
		
		public function GetEventsParametersForEvent(eventId:Number):Array{
			
			var result:SQLResult=null;
			var statement:SQLStatement =null;
			var parameters:Array = new Array();
			
			try{
				statement= new SQLStatement();
				statement.text = BangoConstants.DATABASE_SELECTPOSTEVENTPARAMETERS.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventParameterTableName());
				statement.sqlConnection = _connection;
				statement.parameters[0] = eventId.toString();
				statement.execute();
				result = statement.getResult();
				
				
				for(var i:Object in result.data){
					var obj:Object = result.data[i];
					var pair:Object= new Object();
					pair[BangoFieldNames.POST_PARAMETERNAME] = obj[BangoConstants.DATABASE_COLUMN_EVENTNAME];
					pair[BangoFieldNames.POST_PARAMETERVALUE] = obj[BangoConstants.DATABASE_COLUMN_EVENTVALUE];
					parameters.push(pair);
				}
				
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			return parameters;
		}
			
		public function UpdateEventsPosted(profileId:Number):Boolean{
			
			var returnValue:Boolean = false;
			
			try{
				OpenDatabase();

				execute(BangoConstants.DATABASE_UPDATEEVENTPOSTED.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventTableName()) + profileId);
				execute(BangoConstants.DATABASE_UPDATEPROFILEPOSTED.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName()) + profileId);

			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}

			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
			return returnValue;
		}
		
		public function UpdateProfileLock(profileId:Number,locked:Boolean):Boolean{
			
			var returnValue:Boolean = false;
			
			var statement:SQLStatement =null;
			try{
				OpenDatabase();
				BangoAgent.debug("Lock " + locked.toString() + " " + profileId.toString());
				statement= new SQLStatement();
				statement.text =BangoConstants.DATABASE_UPDATEPROFILELOCK.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName()) + profileId;
				statement.sqlConnection = _connection;
				statement.parameters[0] = locked;
				statement.execute();
				
			}catch(er:Error){BangoAgent.debug(er.message.toString());BangoAgent.debug(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
			return returnValue;
		}
		
		public function DeleteEventsPosted(profileId:Number):Boolean{
			
			var returnValue:Boolean = false;
			try{
				OpenDatabase();
				execute(BangoConstants.DATABASE_DELETEEVENTPARAMETER.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventParameterTableName()).replace(BangoConstants.DATABASE_TABLE_JOINPLACEHOLDER, EventTableName()).replace(BangoConstants.DATABASE_PARAMETER1_PLACEHOLDER, profileId));
				execute(BangoConstants.DATABASE_DELETEEVENT.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, EventTableName()) + profileId);
				execute(BangoConstants.DATABASE_DELETEPROFILE.replace(BangoConstants.DATABASE_TABLE_PLACEHOLDER, ProfileTableName()) + profileId);
				
			}catch(er:Error){trace(er.message.toString());trace(er.getStackTrace())}
			
			if (!_persistDatabaseConnection){
				CloseDatabase();
			}
			
			return returnValue;
		}
		
	}
}